#!/usr/bin/env python
# -*- coding: UTF-8 -*-
'''
@Project ：zhcx-service-test 
@File    ：readjust_prices_sql.py
@Author  ：关依林
@Date    ：2024/1/22 14:34 
'''
from testcases.ProductGrading_Strategy.test001 import contains


# 获取指定渠道SKU字段数据
def getAssignOrderSourceSKU(strategyId, cursor):
    sql = f'SELECT appoints,appoint_type FROM t_strategy_appoint_info WHERE strategy_id = {strategyId}'
    res = cursor.execute_sql_many(sql)
    return res

# 获取listingId下的上架人员ID
def getDataListingPersonId(id, listingId, cursor):
    sql = f'SELECT id,listing_person_id FROM t_amazon_listing_{id} WHERE id IN %s'
    res = cursor.execute_sql_many1(sql,(listingId,))
    return res

# 获取指定部门
def getAssignDepartment(strategyId, cursor):
    sql = f'SELECT listing_dept FROM t_strategy_target WHERE strategy_id = {strategyId}'
    res = cursor.execute_sql_many(sql)[0]["listing_dept"]
    return res

# 获取指定策略和否定策略
def getAssignStrategy(strategyId, cursor):
    lis = []
    sql = f'SELECT relation_strategy_ids,re_relation_strategy_ids FROM t_strategy WHERE id = {strategyId}'
    res = cursor.execute_sql_many(sql)[0]
    lis.append(res["relation_strategy_ids"])
    lis.append(res["re_relation_strategy_ids"])
    return res

# 递归判断是否属于指定部门Listing
def getListingPersonDepartment(idSet, cursor, value):
    temp = [i["listing_person_id"] for i in idSet]
    sql = "SELECT id, user_full_path from rb_admin WHERE id IN %s"
    userPathSet = cursor.execute_sql_many1(sql, (temp,))
    sliceDataTemp = list(filter(lambda x: contains(x["user_full_path"], value) == True, userPathSet))
    idSet = list(filter(lambda x:x["listing_person_id"] in [i["id"] for i in sliceDataTemp], idSet))
    idSet = [i["id"] for i in idSet]
    return idSet


def getAssignListingId(id, cursor, appointType, appointSkuSet):
    field = "sku"
    if appointType == 2:
        field = "msku"
    elif appointType in 3:
        field = "asin"

    sql = f'SELECT id FROM t_amazon_listing_{id} WHERE {field} IN %s;'
    res = cursor.execute_sql_many1(sql, (appointSkuSet,))
    res = [i["id"] for i in res]
    return res

def getOrderSourceSKU(id, cursor, idSet):
    sql = (f"SELECT t1.sku,t1.msku,t1.delivery_type,t2.listing_price,t2.discount_price,t2.discount_start,t2.discount_end "
           f"FROM t_amazon_listing_{id} t1 JOIN t_amazon_listing_amount_{id} t2 "
           f"ON t1.id = t2.id AND t1.customer_id = t2.customer_id WHERE t1.id IN %s;")
    res = cursor.execute_sql_many1(sql, (idSet,))
    return res


def getStrategyCondition(strategyId,cursor):
    sql = f'SELECT condition_field,condition_value  FROM t_strategy_condition WHERE strategy_id = {strategyId}'
    res = cursor.execute_sql_many(sql)
    return res

def getQueueData(listingSet,sourceId,cursor):
    mskuSet = [i["msku"] for i in listingSet]
    sql = f"SELECT msku,type,create_time FROM t_amazon_publish_execute_queue WHERE msku in %s AND source_id = {sourceId}"
    res = cursor.execute_sql_many1(sql, (mskuSet,))
    return res